﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.StorageClient;

namespace Sketchial.Data
{
    public class TableHelper
    {
        #region Cloud Initialization
        private static CloudStorageAccount account;
        private UserDataContext userContext;
        private PostDataContext postContext;
        private ObjectDataContext objectContext;

        public static CloudStorageAccount GetStorageAccount()
        {
            return CloudStorageAccount.Parse(RoleEnvironment.GetConfigurationSettingValue(Properties.Settings.Default.ConnectionString));
        }

        public static bool CreateTableByName(string tableName)
        {
            try
            {
                var acccount = GetStorageAccount();
                var tableAccount = acccount.CreateCloudTableClient();
                tableAccount.CreateTableIfNotExist(tableName);
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion

        #region Constants

        private const string userTablePartitionKey = "user";

        #endregion

        public static bool EnsureTableExists(string tableName)
        {
            try
            {
                var acccount = GetStorageAccount();
                var tableAccount = acccount.CreateCloudTableClient();
                tableAccount.CreateTableIfNotExist(tableName);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool DeleteTable(string tableName)
        {
            try
            {
                var acccount = GetStorageAccount();
                var tableAccount = acccount.CreateCloudTableClient();
                tableAccount.DeleteTableIfExist(tableName);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static CloudTableClient GetServiceTableClient(string accountName)
        {
            return GetStorageAccount().CreateCloudTableClient();
        }

        public static DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
        {
            DataTable dtReturn = new DataTable();

            // column names 
            PropertyInfo[] oProps = null;

            if (varlist == null) return dtReturn;

            EnsureTableExists("Users");

            foreach (T rec in varlist)
            {
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType;

                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }

                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }

                DataRow dr = dtReturn.NewRow();

                foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
                }

                dtReturn.Rows.Add(dr);
            }
            return dtReturn;
        }

        #region User Functions

        public List<UserEntity> ListAllUsers()
        {
            EnsureTableExists("Users");

            userContext = new UserDataContext(GetStorageAccount().TableEndpoint.ToString(), GetStorageAccount().Credentials);
            var result = from u in userContext.UserEntities
                         select u;

            return result.ToList<UserEntity>();
        }

        //public UserEntity GetUserByID(string userID)
        //{
        //    EnsureTableExists("Users");
        //    userContext = new UserDataContext(GetStorageAccount().TableEndpoint.ToString(), GetStorageAccount().Credentials);
        //    var result = from u in userContext.UserEntities
        //                 where u.RowKey == userID && u.PartitionKey == userTablePartitionKey
        //                 select u;

        //    if (result == null)
        //    {
        //        return null;
        //    }

        //    return result.FirstOrDefault<UserEntity>();
        //}

        public UserEntity GetUserByID(string userID)
        {
            EnsureTableExists("Users");
            userContext = new UserDataContext(GetStorageAccount().TableEndpoint.ToString(), GetStorageAccount().Credentials);
            var result = from u in userContext.UserEntities
                         where u.RowKey == userID
                         select u;

            if (result == null)
            {
                return null;
            }
            return result.FirstOrDefault<UserEntity>();
        }

        public UserEntity GetUserByFacebookID(string userID)
        {
            EnsureTableExists("Users");
            userContext = new UserDataContext(GetStorageAccount().TableEndpoint.ToString(), GetStorageAccount().Credentials);
            var result = from u in userContext.UserEntities
                         where u.FacebookID == userID
                         select u;

            if (result == null)
            {
                return null;
            }
            return result.FirstOrDefault<UserEntity>();
        }

        public bool AddNewUser(UserEntity newUser)
        {
            try
            {
                var account = GetStorageAccount();
                var tableClient = account.CreateCloudTableClient();

                EnsureTableExists("Users");

                var context = tableClient.GetDataServiceContext();

                context.AddObject("Users", newUser);
                context.SaveChanges();

                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool UpdateUser(UserEntity user)
        {
            try
            {
                var account = GetStorageAccount();
                var tableClient = account.CreateCloudTableClient();
                EnsureTableExists("Users");
                var context = tableClient.GetDataServiceContext();
                UserEntity usr = (from us in context.CreateQuery<UserEntity>("Users") where us.RowKey == user.RowKey select us).SingleOrDefault();
                usr.Surname = user.Surname;
                usr.Name = user.Name;
                usr.Surname = user.Surname;
                usr.Email = user.Email;
                usr.Gender = user.Gender;
                usr.Comment = user.Comment;
                context.UpdateObject(usr);
                context.SaveChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool DeleteUser(UserEntity user)
        {
            try
            {
                var account = GetStorageAccount();
                var tableClient = account.CreateCloudTableClient();
                EnsureTableExists("Users");
                var context = tableClient.GetDataServiceContext();
                UserEntity usr = (from us in context.CreateQuery<UserEntity>("Users") where us.RowKey == user.RowKey select us).SingleOrDefault();
                context.DeleteObject(usr);
                context.SaveChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion

        public List<ObjectEntity> GetObjectsByClassInList(string objClass)
        {
            EnsureTableExists("Objects");
            objectContext = new ObjectDataContext(GetStorageAccount().TableEndpoint.ToString(), GetStorageAccount().Credentials);
            var result = from u in objectContext.ObjectEntities
                         where u.ObjectClass == objClass
                         select u;

            if (result == null)
            {
                return null;
            }
            return result.ToList();
        }

        public ObjectEntity GetObjectByID(string objid)
        {
            EnsureTableExists("Objects");
            objectContext = new ObjectDataContext(GetStorageAccount().TableEndpoint.ToString(), GetStorageAccount().Credentials);
            var result = from u in objectContext.ObjectEntities
                         where u.ObjectID == objid
                         select u;

            if (result == null)
            {
                return null;
            }
            return result.FirstOrDefault<ObjectEntity>();
        }

        public bool AddNewObject(ObjectEntity newObject)
        {
            try
            {
                var account = GetStorageAccount();
                var tableClient = account.CreateCloudTableClient();

                EnsureTableExists("Objects");

                var context = tableClient.GetDataServiceContext();

                context.AddObject("Objects", newObject);
                context.SaveChanges();

                return true;
            }
            catch
            {
                return false;
            }
        }

        //public DataTable GetObjectsByClass(string objClass)
        //{
        //    EnsureTableExists("Objects");
        //    objectContext = new ObjectDataContext(GetStorageAccount().TableEndpoint.ToString(), GetStorageAccount().Credentials);
        //    var result = from u in objectContext.ObjectEntities
        //                 where u.ObjectClass == objClass
        //                 select u;

        //    if (result == null)
        //    {
        //        return null;
        //    }
        //    return LINQToDataTable<ObjectEntity>(result);
        //}

        public DataTable GetObjectsByClass(string objClass)
        {
            DataTable dt2 = new DataTable();
            dt2.Columns.Add("id");
            dt2.Columns.Add("obj");
            EnsureTableExists("Objects");
            objectContext = new ObjectDataContext(GetStorageAccount().TableEndpoint.ToString(), GetStorageAccount().Credentials);
            var result = from u in objectContext.ObjectEntities
                         where u.ObjectClass == objClass
                         select u;
            List<ObjectEntity> list = result.ToList<ObjectEntity>();
            //Response.Write("The directory contains the following files and directories:" + di.FullName + "<hr>");
            foreach (ObjectEntity item in list)
            {
                DataRow dr2 = dt2.NewRow();
                dr2[0] = item.ObjectID;
                dr2[1] = item.ObjectID + "_0";
                dt2.Rows.Add(dr2);
            }
            return dt2;
        }


    }
}